{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working With SQLite & Win32 In Python\n",
    "In this tutorial we will cover how to import and export data directly from Excel into a databse that we create with SQLite the goal of this tutorial is to introduce how we might dynamically add data to our database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the libraries\n",
    "import sqlite3\n",
    "import win32com.client as win32"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the Database & the Table\n",
    "We will create an in-memory database so that way we aren't creating any files in this tutorial. After that we will need to add a table to our database so we can store the data in it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x20576d0c0a0>"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a memort database & a cursor to control our queries.\n",
    "conn = sqlite3.connect(':memory:')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# create the table to store the data.\n",
    "cursor.execute(\"\"\"CREATE TABLE people (\n",
    "                  name TEXT,\n",
    "                  age INTEGER,\n",
    "                  salary INTEGER,\n",
    "                  gender TEXT\n",
    "                  )\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<b>Tip:</b> You can also use standard database files and the steps will be the same.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define the Queries to Pull and Insert the Data\n",
    "We will need to help functions in order for us to put and pull data to and from the database. The first function is used to query data from the database, its a simple query and will only let us filter the data by age. The second function, is used to insert data into the database, it takes 4 parameters all of which are columns in our table. We use place holders to help format our query and make our function dynamic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a query to retrieve the data\n",
    "def get_by_age(age):\n",
    "    cursor.execute(\"SELECT * FROM people WHERE age > :desiredAge\",{'desiredAge': age})\n",
    "    return cursor.fetchall()\n",
    "\n",
    "# define a query to insert the person\n",
    "def insert_person(name, age, salary, gender):\n",
    "    with conn:\n",
    "         cursor.execute(\"INSERT INTO people VALUES (:name, :age, :salary, :gender)\",\\\n",
    "                        {'name':name,'age':age,'salary':salary,'gender':gender})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the Data From Excel\n",
    "Now that we have the database all taken care of let's get the data from our workbook. All of this should look familair if you've seen the other tutorials, but I did decide to add some variety where we make the code to handle a dynamic range by leveraging the xlToRight & xlDown constants which will allow us to find the last column & row in our data table. Once I get my necessary info, I define the range and store the values in a variable called `raw_data`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('Name', 'Age', 'Salary', 'Gender'),\n",
       " ('Alex', 18.0, Decimal('27035'), 'M'),\n",
       " ('Mike', 36.0, Decimal('34536'), 'M'),\n",
       " ('Sara', 59.0, Decimal('119850'), 'F'),\n",
       " ('Josh', 33.0, Decimal('151446'), 'M'),\n",
       " ('Marshal', 53.0, Decimal('128722'), 'M'),\n",
       " ('Sue', 81.0, Decimal('147950'), 'F'),\n",
       " ('David', 38.0, Decimal('71273'), 'M'),\n",
       " ('Tony', 30.0, Decimal('62963'), 'M'),\n",
       " ('Stephanie', 35.0, Decimal('39199'), 'F'),\n",
       " ('Mary', 63.0, Decimal('69612'), 'F'))"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create our instance of Excel using Early Binding\n",
    "ExcelApp = win32.gencache.EnsureDispatch(\"Excel.Application\")\n",
    "\n",
    "# Get the Workbook\n",
    "ExcelWrkBook = ExcelApp.ActiveWorkbook\n",
    "\n",
    "# Get the Sheet\n",
    "ExcelWrkSht = ExcelWrkBook.ActiveSheet\n",
    "\n",
    "# Get xlConstants\n",
    "xlRight = win32.constants.xlToRight\n",
    "xlDown = win32.constants.xlDown\n",
    "\n",
    "# Get the last row and column\n",
    "LastCol = ExcelWrkSht.Range(\"H1\").End(xlRight).Column\n",
    "LastRow = ExcelWrkSht.Range(\"H1\").End(xlDown).Row\n",
    "\n",
    "# define those cells\n",
    "FirstCell = ExcelWrkSht.Cells(1, 8)\n",
    "LastCell = ExcelWrkSht.Cells(LastRow, LastCol)\n",
    "\n",
    "# get the data\n",
    "raw_data = ExcelWrkSht.Range(FirstCell, LastCell).Value\n",
    "raw_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<b>Tip:</b> Remember that the `Value` property returns a tuple of tuples.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the Data\n",
    "After we grab the data, we will loop through each of the rows and pull the corresponding info. Keep in mind, I skipped the first row because that is just the column headers. After that, I a ran a new query to pull all the people with an age greater than 10. Both the insert & and the query are leveraging the functions we defined up above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Alex', 73, 38921, 'M'),\n",
       " ('Mike', 68, 109294, 'M'),\n",
       " ('Sara', 50, 124908, 'F'),\n",
       " ('Josh', 51, 83313, 'M'),\n",
       " ('Marshal', 78, 64480, 'M')]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# upload the data into the database\n",
    "for row in raw_data[1:]:\n",
    "    \n",
    "    # get the details from each row\n",
    "    name = row[0]\n",
    "    age = row[1]\n",
    "    salary = int(row[2])\n",
    "    gender = row[3]\n",
    "    \n",
    "    # insert the person\n",
    "    insert_person(name, age, salary, gender)\n",
    "\n",
    "# pull the newly inserted data.\n",
    "people = get_by_age(10)\n",
    "people[0:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dump the Data Into Excel\n",
    "With our query complete, we can now drop the data into our workbook. To make sure we can handle queries that return different number of rows. I first define the length of a row of data in our query by selecting the first row and counting the number of elements in it. For the length of the column I simply count the length of the list of data. Once I do this, I define the first and last cell of the range then drop the data into Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[('Alex', 73, 38921, 'M'),\n",
       " ('Mike', 68, 109294, 'M'),\n",
       " ('Sara', 50, 124908, 'F'),\n",
       " ('Josh', 51, 83313, 'M'),\n",
       " ('Marshal', 78, 64480, 'M'),\n",
       " ('Sue', 23, 168095, 'F'),\n",
       " ('David', 17, 146569, 'M'),\n",
       " ('Tony', 58, 55714, 'M'),\n",
       " ('Stephanie', 63, 175390, 'F'),\n",
       " ('Mary', 63, 176367, 'F')]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the length of a row\n",
    "RowLength = len(people[0])\n",
    "display(RowLength)\n",
    "\n",
    "# get the length of the column\n",
    "ColLength = len(people)\n",
    "display(ColLength)\n",
    "\n",
    "# row, column\n",
    "FirstCell = ExcelWrkSht.Cells(2, 1)\n",
    "LastCell = ExcelWrkSht.Cells(1 + ColLength, RowLength)\n",
    "\n",
    "# Get the Range\n",
    "ExcelRng = ExcelWrkSht.Range(FirstCell, LastCell )\n",
    "\n",
    "ExcelRng.Value = people\n",
    "people"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
